import sqlite3
import json
import os
from typing import Optional, Dict, Any, List
from dataclasses import dataclass
from enum import Enum


class DatabaseType(Enum):
    SQLITE = "sqlite"
    MYSQL = "mysql"
    POSTGRESQL = "postgresql"


@dataclass
class DatabaseConfig:
    """数据库配置类"""
    db_type: DatabaseType
    host: Optional[str] = None
    port: Optional[int] = None
    database: str = ""
    username: Optional[str] = None
    password: Optional[str] = None
    file_path: Optional[str] = None  # SQLite文件路径


class DatabaseManager:
    """数据库管理器 - 处理多种数据库连接"""

    def __init__(self):
        self.connection = None
        self.config = None
        self._keepalive_thread = None
        self._keepalive_stop = None
        self._keepalive_interval_seconds = 300  # 5分钟

    def connect(self, config: DatabaseConfig) -> bool:
        """连接到数据库"""
        try:
            self.config = config

            if config.db_type == DatabaseType.SQLITE:
                # SQLite连接
                db_path = config.file_path or config.database
                self.connection = sqlite3.connect(db_path)
                self.connection.row_factory = sqlite3.Row  # 使结果可以按列名访问
                print(f"✅ 成功连接到SQLite数据库: {db_path}")

            elif config.db_type == DatabaseType.MYSQL:
                # MySQL连接 (需要安装 mysql-connector-python)
                try:
                    import mysql.connector
                    self.connection = mysql.connector.connect(
                        host=config.host,
                        port=config.port or 3306,
                        database=config.database,
                        user=config.username,
                        password=config.password
                    )
                    print(f"✅ 成功连接到MySQL数据库: {config.host}:{config.port}/{config.database}")
                except ImportError:
                    print("❌ 需要安装 mysql-connector-python: pip install mysql-connector-python")
                    return False

            elif config.db_type == DatabaseType.POSTGRESQL:
                # PostgreSQL连接 (需要安装 psycopg2)
                try:
                    import psycopg2
                    self.connection = psycopg2.connect(
                        host=config.host,
                        port=config.port or 5432,
                        database=config.database,
                        user=config.username,
                        password=config.password
                    )
                    print(f"✅ 成功连接到PostgreSQL数据库: {config.host}:{config.port}/{config.database}")
                except ImportError:
                    print("❌ 需要安装 psycopg2: pip install psycopg2-binary")
                    return False

            # 启动保活线程
            self._start_keepalive()
            return True

        except Exception as e:
            print(f"❌ 数据库连接失败: {str(e)}")
            return False

    def disconnect(self):
        """断开数据库连接"""
        # 停止保活
        try:
            if self._keepalive_stop:
                self._keepalive_stop.set()
            self._keepalive_stop = None
        except Exception:
            pass

        try:
            if self._keepalive_thread and self._keepalive_thread.is_alive():
                # 不强制 join，避免阻塞关闭
                self._keepalive_thread = None
        except Exception:
            self._keepalive_thread = None

        if self.connection:
            self.connection.close()
            self.connection = None
            print("🔌 数据库连接已断开")

    def execute_query(self, query: str, params: tuple = None) -> List[Dict]:
        """执行查询并返回结果"""
        if not self.connection:
            raise Exception("数据库未连接")

        try:
            cursor = self.connection.cursor()

            if params:
                cursor.execute(query, params)
            else:
                cursor.execute(query)

            # 获取列名
            if cursor.description:
                columns = [desc[0] for desc in cursor.description]
                results = []

                for row in cursor.fetchall():
                    if isinstance(row, sqlite3.Row):
                        # SQLite Row对象
                        results.append(dict(row))
                    else:
                        # 其他数据库
                        results.append(dict(zip(columns, row)))

                cursor.close()
                return results
            else:
                # 没有返回结果的查询（如INSERT, UPDATE, DELETE）
                affected_rows = cursor.rowcount
                cursor.close()
                return [{"affected_rows": affected_rows}]

        except Exception as e:
            print(f"❌ 查询执行失败: {str(e)}")
            raise

    def execute_non_query(self, query: str, params: tuple = None) -> int:
        """执行非查询语句（INSERT, UPDATE, DELETE）"""
        if not self.connection:
            raise Exception("数据库未连接")

        try:
            cursor = self.connection.cursor()

            if params:
                cursor.execute(query, params)
            else:
                cursor.execute(query)

            affected_rows = cursor.rowcount
            self.connection.commit()
            cursor.close()

            return affected_rows

        except Exception as e:
            self.connection.rollback()
            print(f"❌ 语句执行失败: {str(e)}")
            raise

    def get_tables(self) -> List[str]:
        """获取数据库中的所有表名"""
        if not self.connection:
            raise Exception("数据库未连接")

        try:
            if self.config.db_type == DatabaseType.SQLITE:
                query = "SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'"
            elif self.config.db_type == DatabaseType.MYSQL:
                query = "SHOW TABLES"
            elif self.config.db_type == DatabaseType.POSTGRESQL:
                query = "SELECT tablename FROM pg_tables WHERE schemaname = 'public'"

            results = self.execute_query(query)

            # 提取表名
            if self.config.db_type == DatabaseType.SQLITE:
                return [row['name'] for row in results]
            elif self.config.db_type == DatabaseType.MYSQL:
                if not results:
                    return []

                # 获取第一行的所有键，找到正确的表名列
                first_row = results[0]
                table_column = None

                # 尝试不同的可能列名
                possible_keys = [
                    f"Tables_in_{self.config.database}",  # 标准格式
                    f"tables_in_{self.config.database}",  # 小写格式
                    "TABLE_NAME",  # 某些MySQL版本
                    "table_name",  # 小写版本
                ]

                # 如果上述都不匹配，使用第一个键（通常就是表名列）
                for key in possible_keys:
                    if key in first_row:
                        table_column = key
                        break

                if not table_column:
                    # 如果没有找到匹配的键，使用第一个可用的键
                    available_keys = list(first_row.keys())
                    if available_keys:
                        table_column = available_keys[0]
                        print(f"⚠️  使用默认列名: {table_column}")
                    else:
                        print("❌ 无法确定表名列")
                        return []

                return [row[table_column] for row in results]
            elif self.config.db_type == DatabaseType.POSTGRESQL:
                return [row['tablename'] for row in results]

        except Exception as e:
            print(f"❌ 获取表列表失败: {str(e)}")
            if self.config.db_type == DatabaseType.MYSQL and results:
                print(f"🔍 调试信息 - 查询结果的键名: {list(results[0].keys())}")
                print(f"🔍 调试信息 - 数据库名: {self.config.database}")
            return []

    def test_connection(self) -> bool:
        """测试数据库连接"""
        try:
            if self.config.db_type == DatabaseType.SQLITE:
                self.execute_query("SELECT 1")
            elif self.config.db_type in [DatabaseType.MYSQL, DatabaseType.POSTGRESQL]:
                self.execute_query("SELECT 1")

            print("✅ 数据库连接测试成功")
            return True

        except Exception as e:
            print(f"❌ 数据库连接测试失败: {str(e)}")
            return False

    # ---------------- 保活机制 -----------------
    def set_keepalive_interval(self, seconds: int):
        """设置保活间隔秒数（最小30秒）"""
        self._keepalive_interval_seconds = max(30, int(seconds))

    def _start_keepalive(self):
        import threading
        import time

        try:
            if hasattr(self, '_keepalive_stop') and self._keepalive_stop:
                self._keepalive_stop.set()
        except Exception:
            pass

        self._keepalive_stop = None

        def _worker():
            while self.connection and self._keepalive_stop and not self._keepalive_stop.is_set():
                try:
                    if self.config and self.config.db_type == DatabaseType.MYSQL:
                        try:
                            self.connection.ping(reconnect=True, attempts=1, delay=0)
                        except TypeError:
                            self.connection.ping(reconnect=True)
                    else:
                        cursor = self.connection.cursor()
                        cursor.execute("SELECT 1")
                        cursor.fetchall()
                        cursor.close()
                except Exception as e:
                    try:
                        print(f"⚠️ 保活 ping 失败: {e}")
                    except Exception:
                        pass
                self._keepalive_stop.wait(self._keepalive_interval_seconds)

        self._keepalive_thread = threading.Thread(target=_worker, name="db-keepalive", daemon=True)
        self._keepalive_thread.start()


def save_config(config: DatabaseConfig, filename: str = "db_config.json"):
    """保存数据库配置到文件"""
    config_dict = {
        "db_type": config.db_type.value,
        "host": config.host,
        "port": config.port,
        "database": config.database,
        "username": config.username,
        "password": config.password,  # 注意：实际应用中应该加密存储
        "file_path": config.file_path
    }

    with open(filename, 'w', encoding='utf-8') as f:
        json.dump(config_dict, f, indent=2, ensure_ascii=False)

    print(f"✅ 配置已保存到 {filename}")


def load_config(filename: str = "db_config.json") -> Optional[DatabaseConfig]:
    """从文件加载数据库配置"""
    try:
        if not os.path.exists(filename):
            return None

        with open(filename, 'r', encoding='utf-8') as f:
            config_dict = json.load(f)

        return DatabaseConfig(
            db_type=DatabaseType(config_dict["db_type"]),
            host=config_dict.get("host"),
            port=config_dict.get("port"),
            database=config_dict["database"],
            username=config_dict.get("username"),
            password=config_dict.get("password"),
            file_path=config_dict.get("file_path")
        )

    except Exception as e:
        print(f"❌ 加载配置失败: {str(e)}")
        return None


# 演示和测试代码
if __name__ == "__main__":
    print("🚀 数据库管理工具 - 连接模块测试")
    print("=" * 50)

    # 创建数据库管理器
    db_manager = DatabaseManager()

    # 测试SQLite连接
    print("\n📁 测试SQLite连接...")
    sqlite_config = DatabaseConfig(
        db_type=DatabaseType.SQLITE,
        database="test_database.db"
    )

    if db_manager.connect(sqlite_config):
        # 创建测试表
        create_table_sql = """
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            email TEXT UNIQUE NOT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )
        """

        try:
            db_manager.execute_non_query(create_table_sql)
            print("✅ 测试表创建成功")

            # 插入测试数据
            insert_sql = "INSERT INTO users (name, email) VALUES (?, ?)"
            affected = db_manager.execute_non_query(insert_sql, ("张三", "zhangsan@example.com"))
            print(f"✅ 插入了 {affected} 条记录")

            # 查询数据
            select_sql = "SELECT * FROM users"
            results = db_manager.execute_query(select_sql)
            print(f"✅ 查询到 {len(results)} 条记录:")
            for row in results:
                print(f"   ID: {row['id']}, 姓名: {row['name']}, 邮箱: {row['email']}")

            # 获取表列表
            tables = db_manager.get_tables()
            print(f"✅ 数据库中的表: {tables}")

            # 测试连接
            db_manager.test_connection()

        except Exception as e:
            print(f"❌ 测试操作失败: {str(e)}")

        finally:
            db_manager.disconnect()

    # 保存配置示例
    print("\n💾 保存配置示例...")
    save_config(sqlite_config, "sqlite_config.json")

    # 加载配置示例
    print("\n📂 加载配置示例...")
    loaded_config = load_config("sqlite_config.json")
    if loaded_config:
        print(f"✅ 成功加载配置: {loaded_config.db_type.value} - {loaded_config.database}")

    print("\n🎉 数据库连接模块测试完成！")
